

project , uses(../data_output/legislators_klarner.dta)

		
	
	**********
	** Ohio House
	** Consecutive term limites: 4 x 2-year terms.
	** First year: 2000	
	**********
	
	use ../data_output/legislators_klarner.dta, clear
	keep if state=="OH" & chamber=="house"
	keep if regexm(term, "1997|1999|2001|2003|2005|2007|2009|2011|2013|2015")
	gen lastname = trim(regexs(1)) if regexm(lower(CandName),"([^,]+)")
	gen firstname = trim(regexs(1)) if regexm(lower(CandName),",( [^ ])")
	gen mergename = lastname
	bys lastname term: gen n = _n
	by lastname term: egen mean_n=mean(n)
	replace mergename = lastname + " " + firstname if mean_n>1 
	save ../data_output/legislators_OH_house_1997_2016.dta, replace
	
	gen year_minus0 = year	
	gen year_minus2 = year-2
	gen year_minus4 = year-4
	gen year_minus6 = year-6
	tostring year_minus*, replace
	gen s = year_minus6+";" +year_minus4+";"+year_minus2+";"+ year_minus0 + ";"
	gen termlimited = regexm(elections_won, s)==1 & year>=1998	
	save ../data_output/legislators_OH_house_1997_2016.dta, replace
project, creates(../data_output/legislators_OH_house_1997_2016.dta)

project, original(../data_input/OH/bills_2015_2016.xlsx)	
	**bill data
	import excel using ../data_input/OH/bills_2015_2016.xlsx,  clear  first
	foreach s in Rep. Sen. Speaker {
		replace sponsors = subinstr(sponsors, "`s'", "",.)
	}
	split sponsors , generate(sponsor) parse(",")
	drop sponsors 
	reshape long sponsor, i(bill session subject status) j(number)
	drop if sponsor==""
	replace sponsor = trim(lower(sponsor))
	drop number
	drop if regexm(sponsor, "brien|johnson|smith")
	gen law = regexm(lower(status), "enrolled|adopted")
	save ../data_output/bills_by_sponsor_OH_1997_2016.dta, replace

	
project, original(../data_input/OH/johnson_smith_obrien_2015_2016.xlsx)	
	import excel using ../data_input/OH/johnson_smith_obrien_2015_2016.xlsx,  clear  first
	append using ../data_output/bills_by_sponsor_OH_1997_2016.dta
	save ../data_output/bills_by_sponsor_OH_1997_2016.dta, replace
	
	
project, original(../data_input/OH/oh_bills.csv)	
	import delimited using ../data_input/OH/oh_bills.csv,  clear  delimiters(";") bindquotes(strict) encoding("utf-8") varnames(1)
	drop adopted
	gen law = 1 if [ gov_sign!=""  | effective_date!=""  |  (passed_house!="" & passed_senate!="")    ]
	append using ../data_output/bills_by_sponsor_OH_1997_2016.dta
	tostring year, generate(inoffice)
	gen term = ""
	replace term = "1997-1998" if regexm(inoffice, "1997|1998")
	replace term = "1999-2000" if regexm(inoffice, "1999|2000")
	replace term = "2001-2002" if regexm(inoffice, "2001|2002")
	replace term = "2003-2004" if regexm(inoffice, "2003|2004")
	replace term = "2005-2006" if regexm(inoffice, "2005|2006")
	replace term = "2007-2008" if regexm(inoffice, "2007|2008")
	replace term = "2009-2010" if regexm(inoffice, "2009|2010")
	replace term = "2011-2012" if regexm(inoffice, "2011|2012")
	replace term = "2013-2014" if regexm(inoffice, "2013|2014")
	replace term = "2015-2016" if session==131
	gen mergename = subinstr(sponsor, ".","",.)
	save ../data_output/bills_by_sponsor_OH_1997_2016.dta, replace
	
	drop year inoffice
	keep if regexm(lower(bill) , "h")
	save ../data_output/bills_by_sponsor_OH_1997_2016.dta, replace
	
	use ../data_output/bills_by_sponsor_OH_1997_2016.dta, clear
	replace mergename = subinstr(mergename, "'","",.)
	replace mergename = subinstr(mergename, "-","",.)	
	replace mergename = "otterman" if mergename=="otterman j" & term=="2007-2008"
	replace mergename = "johnson" if mergename=="johnson t" & term=="1997-1998"	
	replace mergename = "vanvyven" if mergename=="van vyven" 
	replace mergename = "gardner" if mergename=="gardner rl" 
	replace mergename = "kilbane" if mergename == "conway kilbane" | mergename=="conwaykilbane"
	replace mergename = "hagan" if mergename=="hagan r" & term=="2011-2012"
	replace mergename = "wise" if mergename=="wise m" 
	replace mergename = "jones" if mergename=="jones p" 
	replace mergename = "mcgregor" if mergename=="mcgregor j" & term=="2005-2006"
	replace mergename = "verich" if mergename=="verich m" 
	
		
	merge m:1 mergename term using ../data_output/legislators_OH_house_1997_2016.dta
	//28 observations do not merge
	drop if _merge==1
	save ../data_output/bills_by_sponsor_OH_1997_2016.dta, replace
project, creates(../data_output/bills_by_sponsor_OH_1997_2016.dta)
	
	use ../data_output/bills_by_sponsor_OH_1997_2016.dta, clear
	drop sponsor
	gen sponsor = 1 if bill!=""
	gen sponsor_approp = regexm( cmt_house , "FIN|Finance|Approp") | regexm( cmt_senate , "FIN|Finance|Approp")
	collapse (sum) sponsor* law (first) elections_* terms_in_office win TermLengthActual CandName district chamber state party mergename inoffice year termlimited vtsh, by(CandId term )
	replace law = . if regexm(term, "1997|1999")
	bys term: egen m=sum(sponsor_approp)
	replace sponsor_approp = . if m==0
	save ../data_output/total_bills_by_sponsor_OH_1997_2016.dta, replace
project, creates(../data_output/total_bills_by_sponsor_OH_1997_2016.dta)		

		
	**Voting
	
	use ../data_output/legislators_klarner.dta, clear
	gen lastname = regexs(1) if regexm(lower(CandName),"([^,]+), [^!]")
	gen firstname = regexs(1) if regexm(lower(CandName),"[^,]+, ([^ ])")
	keep if state=="OH" & chamber=="house"
	keep if regexm(term, "2007|2009|2011|2013")
	gen os_mergename = lastname
	bys  os_mergename term: gen n = _n
	by  os_mergename term: egen mean_n=mean(n)
	replace os_mergename = lastname + " " + firstname  if mean_n>1  //firstname  + " " +  lastname //firstname+ ". "
	drop n mean_n
	tempfile tmp
	save `tmp', replace
	
	
project, original(../data_input/OH/oh_votes.csv)	
	import delimited using ../data_input/OH/oh_votes.csv,  clear  delimiters(";") bindquotes(strict) encoding("utf-8") varnames(1)
	keep if chamber=="house"
	tostring session, replace
	gen term = ""
	replace term = "2007-2008" if regexm(session, "127")
	replace term = "2009-2010" if regexm(session, "128")
	replace term = "2011-2012" if regexm(session, "129")
	replace term = "2013-2014" if regexm(session, "130")
	*replace term = "2015-2016" if regexm(session, "2015|2016")
	gen os_mergename = trim(lower(name))
	replace os_mergename = lower(os_mergename)
	replace os_mergename = subinstr(os_mergename,"’","",.)
	replace os_mergename = subinstr(os_mergename,"'","",.)
	replace os_mergename = subinstr(os_mergename,"-","",.)	
	replace os_mergename = "deadrick"	if os_mergename=="deadrick (thomas)" & regexm(term, "2003")
	merge m:1  os_mergename term using `tmp'		
	
	// 2 obs not merging
	drop if _merge==1
	drop _merge
	gen floorvotes = regexm(vote, "yes|no")
	replace vote = "1" if regexm( vote, "yes")
	replace vote = "-1" if regexm( vote, "no")
	replace vote = "0" if vote!="1" & vote!="-1"
	destring vote, replace
	egen vote_id = group(chamber bill motion date location)
	bys party vote_id : egen party_mean=mean(vote)
	gen party_vote = 0
	replace party_vote = 1 if party_mean>0
	replace party_vote = -1 if party_mean<0
	gen vote_party_line = vote==party_vote
	gen vote_against_party = vote==-party_vote
	bys vote_id: egen votemargin = mean(vote)
	gen close_floorvotes = floorvotes if abs(votemargin)<.1			
	
	*floor votes	
	save ../data_output/floorvotes_OH_2007_2014.dta, replace
project, creates(../data_output/floorvotes_OH_2007_2014.dta)	

	use ../data_output/floorvotes_OH_2007_2014.dta, clear
	collapse  (mean) vote_party_line vote_against_party (sum) *floorvotes (first) elections_* terms_in_office win TermLengthActual CandName district chamber state party  inoffice year  vtsh, by(CandId term )
	save ../data_output/total_floorvotes_OH_2007_2014.dta, replace
project, creates(../data_output/total_floorvotes_OH_2007_2014.dta)	
		
